package com.webgis.demo.DAO;
import com.webgis.demo.pojo.Section;
import com.webgis.demo.pojo.SectionMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class SectionSqlDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    public List<SectionMap> getList(int index){
        String stl="SELECT a.id,a.name,b.address_name,b.pointX,b.pointY,a.head_people,a.tel,a.status ,a.grade,a.point_id " +
                "FROM Section a,Map b " +
                "WHERE a.point_id=b.id "+
                "ORDER BY a.id OFFSET "+12*(index-1)+" ROWS FETCH next 12 ROWS ONLY";
        return jdbcTemplate.query(stl, (rs, rowNum) -> {
            SectionMap se=new SectionMap();
            se.setId(rs.getInt("id"));
            se.setAddressName(rs.getString("address_name"));
            se.setPointX(rs.getFloat("pointX"));
            se.setPointY(rs.getFloat("pointY"));
            se.setHandPeople(rs.getString("head_people"));
            se.setName(rs.getString("name"));
            se.setTel(rs.getString("tel"));
            se.setStatus(rs.getString("status"));
            se.setGrade(rs.getString("grade"));
            se.setPointId(rs.getInt("point_id"));
            return se;
        });
    }

    public List<SectionMap> findKeyList(String keyname,int index){
        String stl="SELECT a.id,a.name,b.address_name,b.pointX,b.pointY,a.head_people,a.tel,a.status ,a.grade,a.point_id " +
                "FROM Section a,Map b " +
                "WHERE a.point_id=b.id AND (b.address_name LIKE "+"'%"+keyname+"%' "+
                "OR a.name LIKE "+"'%"+keyname+"%' "+"OR a.head_people LIKE "+
                "'%"+keyname+"%' "+"OR a.status LIKE "+"'%"+keyname+"%') "+
                "ORDER BY a.id OFFSET "+12*(index-1)+" ROWS FETCH next 12 ROWS ONLY";
        return jdbcTemplate.query(stl ,(rs, rowNum) -> {
            SectionMap se=new SectionMap();
            se.setId(rs.getInt("id"));
            se.setAddressName(rs.getString("address_name"));
            se.setPointX(rs.getFloat("pointX"));
            se.setPointY(rs.getFloat("pointY"));
            se.setHandPeople(rs.getString("head_people"));
            se.setName(rs.getString("name"));
            se.setTel(rs.getString("tel"));
            se.setStatus(rs.getString("status"));
            se.setGrade(rs.getString("grade"));
            se.setPointId(rs.getInt("point_id"));
            return se;
        });
    }

    public Integer findKeySum(String keyname){
        String sql="SELECT COUNT(*)\n" +"FROM Section a,Map b " +
                "WHERE a.point_id=b.id AND (b.address_name LIKE "+"'%"+keyname+"%' "+
                "OR a.name LIKE "+"'%"+keyname+"%' "+"OR a.head_people LIKE "+
                "'%"+keyname+"%' "+"OR a.status LIKE "+"'%"+keyname+"%') ";
        return jdbcTemplate.queryForObject(sql,Integer.class);

    }

    public Integer findSum(){
        String sql="SELECT rows FROM sysindexes\n" +
                "WHERE id = OBJECT_ID('[dbo].[Section]') AND indid < 2";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    public Integer findMaxId(){
        String sql="SELECT MAX(id) FROM Section";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    public int updateSection(Section m){
        String sql="UPDATE Section SET name=?, point_id=?, head_people=?,tel=?,status=?,grade=? WHERE id=?";
        return jdbcTemplate.update(sql,m.getName(),m.getPointId(),m.getHandPeople(),m.getTel(),m.getStatus(),m.getGrade(),m.getId());
    }

    public int insertIntoSection(Section M){
        String sql = "INSERT INTO Section(id,name,point_id,head_people,tel,status,grade) VALUES(?,?,?,?,?,?,?)";
        assert false;
        return jdbcTemplate.update(sql,M.getId(),M.getName(),M.getPointId(),M.getHandPeople(),M.getTel(),
        M.getStatus(),M.getGrade());
    }

    public void deleteSection(int id){
        String sql ="DROP TABLE IF EXISTS MonitorData_"+id;
        jdbcTemplate.execute(sql);
        sql ="DELETE FROM Section WHERE id="+id;
        jdbcTemplate.execute(sql);
    }

    public void crateMonitorData(int id){
        String sql="USE MyWebgis\n" +
                "CREATE TABLE MonitorData_"+id+"(\n" +
                "\tid int NOT NULL PRIMARY KEY IDENTITY(1,1) ,\n" +
                "\tsection_id int NOT NULL,\n" +
                "\tmd_year int NOT NULL ,\n" +
                "\tmd_month int NOT NULL,\n" +
                "\tmd_day int NOT NULL ,\n" +
                "\tmd_hour int NOT NULL,\n" +
                "\tmd_minute int NOt NULL,\n" +
                "\tmd_second int NOT NULL,\n" +
                "\tmd_temperate float(24),\n" +
                "\tmd_height float(24),\n" +
                "\tmd_flow float(24),\n" +
                "\tFOREIGN KEY (section_id) REFERENCES Section(id)\n" +
                "\t)";
        jdbcTemplate.execute(sql);
    }
}
